package ATM.data;

//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
//import java.sql.ResultSetMetaData;
//import java.sql.Statement;
//import java.util.ArrayList;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

import javax.annotation.processing.RoundEnvironment;

import ATM.gui.Screen;

public class AdminData extends AtmData {
	private PreparedStatement InsertIntoID = null;
	private PreparedStatement insertIntoCustomers = null;
	private PreparedStatement updatePassword = null;
	private PreparedStatement updateCustomer = null;
	private PreparedStatement selectCustomer = null;
	private PreparedStatement selectAllCustomers = null;
	private PreparedStatement addAccount = null;
	private PreparedStatement deleteAccount = null;
	private PreparedStatement selectAccounts = null;
	private PreparedStatement selectAccountsForCustomer = null;

	public AdminData(Screen screen) {
		super(screen);
		try {
			InsertIntoID = connection
					.prepareStatement("INSERT INTO ID (Password,Identification) VALUES (12345, 'Customer')");
			insertIntoCustomers = connection
					.prepareStatement("INSERT INTO Customers ( CustomerID, FirstName, LastName, CPR, Address, Zipcode, City, Country, Phonenumber, Email) "
							+ "VALUES ( ?, ?, ?, ?,? ,? ,? ,? ,?,?) ;");
			updatePassword = connection
					.prepareStatement("UPDATE ID SET Password = ? WHERE ID = ? ");
			updateCustomer = connection
					.prepareStatement("UPDATE customers SET FirstName = ?, LastName = ?, CPR = ?,  Address = ?, Zipcode = ?, City = ?, Country = ?, Phonenumber = ?, Email = ?  WHERE CustomerID = ?");
			selectAllCustomers = connection
					.prepareStatement("(SELECT * FROM id INNER JOIN Customers ON id.ID = Customers.CustomerID  WHERE Identification = 'Customer')");
			selectCustomer = connection
					.prepareStatement("SELECT * FROM id INNER JOIN Customers ON id.ID = Customers.CustomerID  WHERE Identification = 'Customer' AND ID = ?");
			addAccount = connection
					.prepareStatement("INSERT INTO Accounts (CustomerID, AccountType, AvailableBalance, TotalBalance, Interest, Overdraft) VALUES (?,?,?,?,?,?)");

			deleteAccount = connection
					.prepareStatement("DELETE FROM Accounts WHERE AccountID = ?");

			selectAccounts = connection
					.prepareStatement("SELECT * FROM Accounts");

			selectAccountsForCustomer = connection
					.prepareStatement("SELECT * FROM Accounts WHERE CustomerID = ?");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int newCustomerID() {
		int customerID = 0;

		try {
			InsertIntoID.executeUpdate();

			resultSet = statement
					.executeQuery("SELECT * FROM ID WHERE Password = 12345");
			resultSet.next();
			customerID = resultSet.getInt("ID");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return customerID;
	}

	public boolean addCustomer(int customerID, String firstName,
			String lastName, String cpr, String address, String city,
			int zipcode, String country, int phonenumber, String mail) {
		int i = 0;
		Random rn = new Random();
		boolean b = false;

		try {
			insertIntoCustomers.setInt(1, customerID);
			insertIntoCustomers.setString(2, firstName);
			insertIntoCustomers.setString(3, lastName);
			insertIntoCustomers.setString(4, cpr);
			insertIntoCustomers.setString(5, address);
			insertIntoCustomers.setInt(6, zipcode);
			insertIntoCustomers.setString(7, city);
			insertIntoCustomers.setString(8, country);
			insertIntoCustomers.setInt(9, phonenumber);
			insertIntoCustomers.setString(10, mail);
			i = insertIntoCustomers.executeUpdate();

			if (i != 0) {
				updatePassword.setInt(2, customerID);
				updatePassword.setInt(1, rn.nextInt(99999));
				i = updatePassword.executeUpdate();
			}

		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
		}
		if (i != 0) {
			b = true;
		}
		return b;
	} // end method

	public Boolean AddAccount(int customerid, String accountType,
			double inRate, double ovDraft) {
		Boolean b = false;
		try {
			addAccount.setInt(1, customerid);
			addAccount.setString(2, accountType);
			addAccount.setDouble(3, 0);
			addAccount.setDouble(4, 0);
			addAccount.setDouble(5, inRate);
			addAccount.setDouble(6, ovDraft);
			int i = addAccount.executeUpdate();
			if (i == 1) {
				b = true;
			}
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
		}
		return b;

	}

	public void DeleteAccount(int accountid) {
		try {
			deleteAccount.setInt(1, accountid);
			deleteAccount.executeUpdate();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
		}

	}

	public boolean CustomerExits(int customerID) {
		boolean b = false;
		try {
			selectCustomer.setInt(1, customerID);
			selectCustomer.execute();
			resultSet = selectCustomer.executeQuery();
			b = resultSet.next();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;

	}

	public ArrayList<Customers> getCustomer(int customerID) {

		ArrayList<Customers> results = null;

		try {

			selectCustomer.setInt(1, customerID);
			selectCustomer.execute();

			resultSet = selectCustomer.executeQuery();
			results = new ArrayList<Customers>();

			resultSet.next();
			results.add(new Customers(resultSet.getInt("CustomerID"), resultSet
					.getInt("password"), resultSet.getString("FirstName"),
					resultSet.getString("LastName"),
					resultSet.getString("CPR"), resultSet.getString("Address"),
					resultSet.getInt("Zipcode"), resultSet.getString("City"),
					resultSet.getString("Country"), resultSet
							.getInt("Phonenumber"), resultSet
							.getString("Email")));

		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
			closeR();

		}
		return results;

	}

	public ArrayList<Customers> getCustomer() {
		ArrayList<Customers> results = null;
		try {
			selectAllCustomers.execute();
			resultSet = selectAllCustomers.executeQuery();
			results = new ArrayList<Customers>();

			while (resultSet.next()) {
				results.add(new Customers(resultSet.getInt("CustomerID"),
						resultSet.getInt("password"), resultSet
								.getString("FirstName"), resultSet
								.getString("LastName"), resultSet
								.getString("CPR"), resultSet
								.getString("Address"), resultSet
								.getInt("Zipcode"),
						resultSet.getString("City"), resultSet
								.getString("Country"), resultSet
								.getInt("Phonenumber"), resultSet
								.getString("Email")));
			}
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
			closeR();

		}
		return results;

	}

	public void addInterest() {

		try {
			// selectAccounts.execute();
			resultSet = selectAccounts.executeQuery();

			while (resultSet.next()) {
				double availableBalance = resultSet
						.getDouble("AvailableBalance");
				double totalBalance = resultSet.getDouble("TotalBalance");
				double interest = resultSet.getDouble("Interest");
				int accountID = resultSet.getInt("AccountID");

				// if account has negative balance, the interest will increase
				if (availableBalance < 0) {
					interest = interest + 12.5;
				}// end if statement

				// x equals the interest in amount for the available balance
				double x = (availableBalance * Math.pow((1 + (interest / 100)),
						1)) - availableBalance;
				availableBalance += x;
				totalBalance += x;
				
				availableBalance = Round(availableBalance);
				totalBalance = Round(totalBalance);
				updateAvBal.setDouble(1, availableBalance);
				updateAvBal.setInt(2, accountID);
				updateAvBal.executeUpdate();

				updateToBal.setDouble(1, totalBalance);
				updateToBal.setInt(2, accountID);
				updateToBal.executeUpdate();
			}
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
			closeR();

		}

	}

	public ArrayList<Accounts> getAccountsForCustomer(int customerID) {
		ArrayList<Accounts> results = new ArrayList<Accounts>();
		try {
			selectAccountsForCustomer.setInt(1, customerID);
			selectAccountsForCustomer.execute();
			resultSet = selectAccountsForCustomer.executeQuery();
			while (resultSet.next()) {
				results.add(new Accounts(resultSet.getInt("AccountID"),
						resultSet.getString("AccountType"), resultSet
								.getDouble("AvailableBalance"), resultSet
								.getDouble("TotalBalance"), resultSet
								.getDouble("Overdraft"), resultSet
								.getDouble("Interest")));
			}
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			close();
		}

		return results;
	}

	public boolean EditCustomer(ArrayList<Customers> customer) {
		boolean b = false;
		int i = 0;
		try {
			updateCustomer.setString(1, customer.get(0).getFirstName());
			updateCustomer.setString(2, customer.get(0).getLastName());
			updateCustomer.setString(3, customer.get(0).getCpr());
			updateCustomer.setString(4, customer.get(0).getAddress());
			updateCustomer.setInt(5, customer.get(0).getZipCode());
			updateCustomer.setString(6, customer.get(0).getCity());
			updateCustomer.setString(7, customer.get(0).getCountry());
			updateCustomer.setInt(8, customer.get(0).getNumber());
			updateCustomer.setString(9, customer.get(0).getMail());
			updateCustomer.setInt(10, customer.get(0).getCustomerID());
			i = updateCustomer.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			close();
		}
		if (i == 1) {
			b = true;
		}
		return b;

	}

	public double Round(double value) {
		value = value * 100;
		long tmp = Math.round(value);
		return (double) tmp / 100;
	}
}
